package edu.ccdb.irods;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import edu.ccdb.irods.dataType.DataSectionType;
import edu.ccdb.irods.dataType.DataValue;
import edu.ccdb.irods.dataType.IRODSFileString;
import edu.ccdb.irods.dataType.PurposeValue;


public class SQLQuery {
	
    public static List<DataValue> getDataSectionID ( String irodsUsername, Integer inSearch, Integer MPID ) throws Exception {
        List<DataValue> result = new ArrayList<DataValue>();
        
//        String sql = "select m.mpid, r.image_set_id, rec.recon_id, " +
//                "seg.seg_id from microscopy_products m left join image_sets r " +
//                "on (r.mproduct_id = m.mpid) left join image_sets_used iu " +
//                    "on (r.image_set_id = iu.image_set_id) " +
//                    "left join reconstructions rec on " +
//                    "(iu.recon_id = rec.recon_id) " +
//                    "left join member_reconstructions mr " +
//                    "on (rec.recon_id = mr.recon_id) " +
//                    "left join super_reconstructions sr " +
//                    "on (mr.super_recon_id = sr.super_recon_id) " +
//                    "left join segmentation_sets segS " +
//                    "on (sr.super_recon_id = segS.super_recon_id) " +
//                    "left join segmentation seg " +
//                    "on (segS.segmentation_set_id = seg.segmentation_set_id) " +
//                    "where m.mpid in" +
//                    "(select v.mpid from ccdb_permission_view v where " +
//                    "v.portal_screenname = ? and v.canwrite = true) ";

        String sql = "select vcd.mpid, vcd.image_set_id, vcd.recon_id, vcd.seg_id  from ccdb_data_section_view vcd, ccdb_permission_view v " +
        		"where vcd.mpid = v.mpid and v.canwrite= true and v.portal_screenname = ?  and vcd.mpid = ? " ;
        
        Connection c = CeeCeeDBService.getConnection();
        PreparedStatement ps = c.prepareStatement(sql);
        
        ps.setString(1, irodsUsername );
//        ps.setInt(2, reconID);
        ps.setInt(2 , MPID);
        
        ResultSet rs = ps.executeQuery();
        
        while (rs.next()){
        	
        	if ( rs.getObject(2)!= null && rs.getInt(2) == inSearch ) {
        		result.add(  new DataValue ( DataSectionType.ImageSetID, rs.getInt(2) ) );
        	}        	
        	if (rs.getObject(3)!= null && rs.getInt(3) == inSearch ) {
        		result.add( new DataValue ( DataSectionType.ReconID, rs.getInt(3)) );
        	}
        	if (rs.getObject(4)!= null && rs.getInt(4) == inSearch ) {
        		result.add( new DataValue ( DataSectionType.SegID , rs.getInt(4) ) );
        	}        	
        }
        
        return result;
    }

    public static PurposeValue getPurposeValue ( int purposeID ) throws Exception {
        
    	PurposeValue result = null;

        String sql = "select data_section_id, data_purpose_id, purpose_name, table_pk_col from ccdb_data_purpose " +
        			"where data_purpose_id = ? " +
        			"order by data_purpose_id ";

        Connection c = CeeCeeDBService.getConnection();
        PreparedStatement ps = c.prepareStatement(sql);
        ps.setInt(1, purposeID);
        
        ResultSet rs = ps.executeQuery();
        
        if (rs.next() ) {
        	result = new PurposeValue(rs.getString(4), rs.getString(3), rs.getInt(2) );
        }

        CeeCeeDBService.closeConnection(c);        
        return result;
    }
	
    public static void manageInsertCCDBTag (Integer MPID, IRODSFileString file, 
			PurposeValue purposeValue, DataValue dataValue) throws Exception {
    	
    	int count = getCCDBDataMapping (file.getAbsolutePath() ) ;
    	
    	if (count != 0 	) {// delete then insert
    		removeCCDBTagWithPath (file.getAbsolutePath());
    	}
    	
    	insertTagCCDB(MPID, file, purposeValue, dataValue);
    }

    private static int getCCDBDataMapping (String file_path ) throws SQLException {
    	
    	int result = 0;
    	
    	String sql = " select count(*) from ccdb_data_mapping where file_path = ? " ;
    	
    	Connection c = CeeCeeDBService.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	ps.setString(1, file_path);
    	
    	ResultSet rs = ps.executeQuery();
    	
    	if (rs.next()) {
    		result = rs.getInt(1);
    	}
    	
    	CeeCeeDBService.closeConnection(c);
    	
    	return result;
    }
    
    private static void removeCCDBTagWithPath (String file_path ) throws SQLException {
    	
    	String sql = "delete from ccdb_data_mapping where file_path = ? ";
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	ps.setString(1, file_path);
    	
    	ps.executeUpdate();
    	
    	CeeCeeDBService.closeConnection(c);
    }
    
    private static void deleteEntry ( IRODSFileString file, DataSectionType type ) throws Exception {
    	
    	// construction the SQL 
    	String sql = "delete from ccdb_data_mapping where file_path = ? ";
    	
    	String typeSQL = null;
    	
    	if (type == DataSectionType.ImageSetID ) 
    		typeSQL = "and image_set_id is not null " ;
    	if (type == DataSectionType.ReconID)
    		typeSQL = "and recon_id is not null " ;
    	if (type == DataSectionType.SegID ) 
    		typeSQL = "and seg_id is not null " ;
    	
    	sql += typeSQL;
    	
    	// construct the 
    	Connection c = CeeCeeDBService.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	ps.setString(1, file.getAbsolutePath());
    	ps.executeUpdate();
    	
    	CeeCeeDBService.closeConnection(c);
    }
    
    private  static void insertTagCCDB ( Integer MPID, IRODSFileString file, 
			PurposeValue purposeValue, DataValue dataValue ) throws SQLException, Exception {

		String sql = "insert into ccdb_data_mapping (ccdb_data_mapping_id, mpid, purpose_id, image_set_id, recon_id, seg_id, file_path, confirmed) " +
			"values(nextval('general_sequence'), ?, ?, ?, ?, ?, ?, true)  ";
		
		Connection c = CeeCeeDBService.getConnection();
		
		PreparedStatement ps = c.prepareStatement(sql);
		
		ps.setInt(1, MPID);
		ps.setInt(2, purposeValue.getPurposeID() );
		
		if ( dataValue.getType() == DataSectionType.ImageSetID ) {
		
		// delete entry before adding
		deleteEntry ( file, DataSectionType.ImageSetID );
		
		ps.setInt (3, dataValue.getValue() );
		ps.setNull(4, Types.INTEGER);
		ps.setNull(5, Types.INTEGER);
		}
		
		if ( dataValue.getType() == DataSectionType.ReconID ) {
		
		//delete entry before adding
		deleteEntry(file, DataSectionType.ReconID );
		
		ps.setNull(3, Types.INTEGER);
		ps.setInt(4, dataValue.getValue());
		ps.setNull(5, Types.INTEGER);
		}
		
		if (dataValue.getType() == DataSectionType.SegID ) {
		
		// delete entry before adding
		deleteEntry(file, DataSectionType.SegID);
		
		ps.setNull(3, Types.INTEGER);
		ps.setNull(4, Types.INTEGER);
		ps.setInt(5, dataValue.getValue());
		}
		ps.setString(6, file.getAbsolutePath() );
		
		ps.executeUpdate();
		
		CeeCeeDBService.closeConnection(c);
    }    
}
